-- 1.创建项目间接成本值表自增序列
declare v_max_id int;
        -- 定义sql语句
        v_sql nvarchar(5000);
begin
	-- 删除序列
	set v_sql = 'drop sequence if exists cost.seq_pro_cost_cal_serve_indirect';
    execute immediate v_sql;

    -- 创建序列
    set v_sql = 'create sequence cost.seq_pro_cost_cal_serve_indirect increment by 1 maxvalue 999999999999 minvalue 1';
	execute immediate v_sql;

	select nvl(max(id) + 1, 1) into v_max_id from cost.pro_cost_cal_serve_indirect where 1 = 1;

    -- 更改步长(increment)为一个你想让序列增加到的值
    set v_sql = concat('alter sequence cost.seq_pro_cost_cal_serve_indirect increment by ', v_max_id);
    execute immediate v_sql;

    -- 执行一下查询序列的语句
    set v_sql = 'select cost.seq_pro_cost_cal_serve_indirect.nextval from dual';
    execute immediate v_sql;

    -- 修改为原来的步长
    set v_sql = 'alter sequence cost.seq_pro_cost_cal_serve_indirect increment by 1';
    execute immediate v_sql;
end;

-- 2.项目间接成本值表重命名
alter table pro_cost_cal_serve_indirect rename to pro_cost_cal_serve_indirect_old;

-- 3.创建项目间接成本值表分区表
create table cost.pro_cost_cal_serve_indirect(
    id int default cost.seq_pro_cost_cal_serve_indirect.nextval not null,
    cal_res_id int null,
    check_scheme_id int null,
    organization_id int null,
    hospital_structure_id int null,
    organization_cal_method int null,
    serve_item_code nvarchar(50) null,
    cost_item_code nvarchar(20) null,
    number decimal(18,4) null,
    price decimal(18,4) null,
    indirect_cost_bk decimal(30,18) null,
    indirect_cost_gl decimal(30,18) null,
    indirect_cost_yf decimal(30,18) null,
    medical_cost_type int null,
    create_user_code nvarchar(20) null,
    create_time timestamp null,
    update_user_code nvarchar(20) null,
    update_time timestamp null,
    delete_flag tinyint null,
    calculate_method int null,
    medical_signage int null
) PARTITION BY LIST (check_scheme_id)
  (PARTITION p_csi_1 VALUES(1) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_2 VALUES(2) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_3 VALUES(3) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_4 VALUES(4) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_5 VALUES(5) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_6 VALUES(6) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_7 VALUES(7) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_8 VALUES(8) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_9 VALUES(9) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_10 VALUES(10) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_11 VALUES(11) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_12 VALUES(12) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_13 VALUES(13) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_14 VALUES(14) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_15 VALUES(15) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_16 VALUES(16) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_17 VALUES(17) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_18 VALUES(18) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_19 VALUES(19) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_20 VALUES(20) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_21 VALUES(21) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_22 VALUES(22) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_23 VALUES(23) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_24 VALUES(24) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_25 VALUES(25) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_26 VALUES(26) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_27 VALUES(27) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_28 VALUES(28) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_29 VALUES(29) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_30 VALUES(30) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_31 VALUES(31) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_32 VALUES(32) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_33 VALUES(33) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_34 VALUES(34) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_35 VALUES(35) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_36 VALUES(36) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_37 VALUES(37) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_38 VALUES(38) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_39 VALUES(39) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_40 VALUES(40) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_41 VALUES(41) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_42 VALUES(42) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_43 VALUES(43) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_44 VALUES(44) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_45 VALUES(45) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_46 VALUES(46) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_47 VALUES(47) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_48 VALUES(48) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_49 VALUES(49) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_50 VALUES(50) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_51 VALUES(51) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_52 VALUES(52) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_53 VALUES(53) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_54 VALUES(54) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_55 VALUES(55) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_56 VALUES(56) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_57 VALUES(57) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_58 VALUES(58) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_59 VALUES(59) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_60 VALUES(60) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_61 VALUES(61) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_62 VALUES(62) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_63 VALUES(63) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_64 VALUES(64) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_65 VALUES(65) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_66 VALUES(66) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_67 VALUES(67) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_68 VALUES(68) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_69 VALUES(69) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_70 VALUES(70) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_71 VALUES(71) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_72 VALUES(72) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_73 VALUES(73) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_74 VALUES(74) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_75 VALUES(75) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_76 VALUES(76) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_77 VALUES(77) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_78 VALUES(78) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_79 VALUES(79) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_80 VALUES(80) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_81 VALUES(81) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_82 VALUES(82) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_83 VALUES(83) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_84 VALUES(84) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_85 VALUES(85) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_86 VALUES(86) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_87 VALUES(87) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_88 VALUES(88) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_89 VALUES(89) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_90 VALUES(90) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_91 VALUES(91) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_92 VALUES(92) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_93 VALUES(93) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_94 VALUES(94) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_95 VALUES(95) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_96 VALUES(96) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_97 VALUES(97) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_98 VALUES(98) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_99 VALUES(99) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_100 VALUES(100) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_101 VALUES(101) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_102 VALUES(102) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_103 VALUES(103) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_104 VALUES(104) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_105 VALUES(105) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_106 VALUES(106) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_107 VALUES(107) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_108 VALUES(108) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_109 VALUES(109) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_110 VALUES(110) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_111 VALUES(111) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_112 VALUES(112) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_113 VALUES(113) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_114 VALUES(114) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_115 VALUES(115) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_116 VALUES(116) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_117 VALUES(117) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_118 VALUES(118) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_119 VALUES(119) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_120 VALUES(120) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_121 VALUES(121) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_122 VALUES(122) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_123 VALUES(123) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_124 VALUES(124) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_125 VALUES(125) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_126 VALUES(126) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_127 VALUES(127) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_128 VALUES(128) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_129 VALUES(129) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_130 VALUES(130) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_131 VALUES(131) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_132 VALUES(132) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_133 VALUES(133) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_134 VALUES(134) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_135 VALUES(135) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_136 VALUES(136) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_137 VALUES(137) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_138 VALUES(138) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_139 VALUES(139) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_140 VALUES(140) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_141 VALUES(141) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_142 VALUES(142) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_143 VALUES(143) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_144 VALUES(144) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_145 VALUES(145) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_146 VALUES(146) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_147 VALUES(147) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_148 VALUES(148) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_149 VALUES(149) STORAGE(ON "MAIN", CLUSTERBTR),
  PARTITION p_csi_150 VALUES(150) STORAGE(ON "MAIN", CLUSTERBTR)
) STORAGE(ON "MAIN", CLUSTERBTR);

-- 4.增加注释
comment on table cost.pro_cost_cal_serve_indirect is '项目间接成本值表';
comment on column cost.pro_cost_cal_serve_indirect.id is '主键id';
comment on column cost.pro_cost_cal_serve_indirect.cal_res_id is '项目成本核算主表id';
comment on column cost.pro_cost_cal_serve_indirect.check_scheme_id is '核算方案主表id';
comment on column cost.pro_cost_cal_serve_indirect.organization_id is '科室id';
comment on column cost.pro_cost_cal_serve_indirect.hospital_structure_id is '院区id';
comment on column cost.pro_cost_cal_serve_indirect.organization_cal_method is '核算科室采用核算方法id';
comment on column cost.pro_cost_cal_serve_indirect.serve_item_code is '服务项目编码';
comment on column cost.pro_cost_cal_serve_indirect.cost_item_code is '成本项目编码';
comment on column cost.pro_cost_cal_serve_indirect.number is '服务项目数量';
comment on column cost.pro_cost_cal_serve_indirect.price is '服务项目单价';
comment on column cost.pro_cost_cal_serve_indirect.indirect_cost_bk is '间接成本_本科间接';
comment on column cost.pro_cost_cal_serve_indirect.indirect_cost_gl is '间接成本_管理分摊';
comment on column cost.pro_cost_cal_serve_indirect.indirect_cost_yf is '间接成本_医辅分摊';
comment on column cost.pro_cost_cal_serve_indirect.medical_cost_type is '成本维度';
comment on column cost.pro_cost_cal_serve_indirect.create_user_code is '创建人编码';
comment on column cost.pro_cost_cal_serve_indirect.create_time is '创建时间';
comment on column cost.pro_cost_cal_serve_indirect.update_user_code is '修改人编码';
comment on column cost.pro_cost_cal_serve_indirect.update_time is '修改时间';
comment on column cost.pro_cost_cal_serve_indirect.delete_flag is '删除标记:0.存在,1.已删除';
comment on column cost.pro_cost_cal_serve_indirect.calculate_method is '计算方法:1303.当量法,1801.时间驱动作业成本法';
comment on column cost.pro_cost_cal_serve_indirect.medical_signage is '医护标识:4301.医生项目,4302.护理项目';

-- 5.删除和重建索引
declare v_idx_sql nvarchar(5000); -- 定义sql语句
begin
	-- 删除索引idx_pro_cost_cal_serve_indirect
	set v_idx_sql = 'drop index if exists idx_pro_cost_cal_serve_indirect';
    execute immediate v_idx_sql;

    -- 创建索引idx_pro_cost_cal_serve_indirect
    set v_idx_sql = 'create index idx_pro_cost_cal_serve_indirect on cost.pro_cost_cal_serve_indirect(id)';
    execute immediate v_idx_sql;

    -- 删除索引idx_non_clu_pro_cost_cal_serve_indirect
    set v_idx_sql = 'drop index if exists idx_non_clu_pro_cost_cal_serve_indirect';
    execute immediate v_idx_sql;

    -- 创建索引idx_non_clu_pro_cost_cal_serve_indirect
    set v_idx_sql = 'create index idx_non_clu_pro_cost_cal_serve_indirect on cost.pro_cost_cal_serve_indirect(check_scheme_id, organization_id)';
    execute immediate v_idx_sql;

    -- 删除索引idx_non_clu_pro_cost_cal_serve_indirect2
    set v_idx_sql = 'drop index if exists idx_non_clu_pro_cost_cal_serve_indirect2';
    execute immediate v_idx_sql;

    -- 创建索引idx_non_clu_pro_cost_cal_serve_indirect2
    set v_idx_sql = 'create index idx_non_clu_pro_cost_cal_serve_indirect2 on cost.pro_cost_cal_serve_indirect(check_scheme_id, cal_res_id)';
    execute immediate v_idx_sql;

end;

-- 6.项目间接成本值表数据迁移
insert into cost.pro_cost_cal_serve_indirect(
    id, cal_res_id, check_scheme_id, organization_id,
	hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	number, price, indirect_cost_bk, indirect_cost_gl,
	indirect_cost_yf, medical_cost_type, create_user_code, create_time,
	update_user_code, update_time, delete_flag, calculate_method,
	medical_signage
)
select id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
       number, price, indirect_cost_bk, indirect_cost_gl,
       indirect_cost_yf, medical_cost_type, create_user_code, create_time,
       update_user_code, update_time, delete_flag, calculate_method,
       medical_signage
from cost.pro_cost_cal_serve_indirect_old;
commit;

-- 7.删除原项目间接成本值表
drop table if exists cost.pro_cost_cal_serve_indirect_old;

-- 8.查看表分区是否存在
select count(1)
        from user_tab_partitions
        where lower(table_name) = 'pro_cost_cal_serve_indirect'
          and lower(partition_name) = 'p_csi_100';

select count(1) from user_tab_partitions where lower(table_name) = 'pro_cost_cal_serve_indirect' and lower(partition_name) = 'p_csi_86'；

-- 9.增加分区
ALTER TABLE cost.pro_cost_cal_serve_indirect ADD PARTITION p_csi_100 VALUES(100);

alter table cost.pro_cost_cal_serve_indirect add partition p_csi_86 values(86);

-- 10.删除分区
    使用 drop partition 可将分区的定义连同数据一起删除，示例语句如下：
    alter table cost.pro_cost_cal_serve_indirect drop partition p_csi_100;
    使用 truncate partition 仅删除分区的数据，示例语句如下：
    alter table cost.pro_cost_cal_serve_indirect truncate partition p_csi_100;
	
-- 11.指定分区写入数据
insert
	into
	cost.pro_cost_cal_serve_indirect partition(p_csi_86) (cal_res_id,
	check_scheme_id,
	organization_id,
	organization_cal_method,
	serve_item_code,
	cost_item_code,
	number,
	price,
	indirect_cost_bk,
	indirect_cost_gl,
	indirect_cost_yf,
	calculate_method,
	medical_signage,
	medical_cost_type,
	hospital_structure_id,
	create_time,
	update_time,
	delete_flag )
values (?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?)

-- 12.指定分区查询数据
select
	row_number() over(
	order by t1.transfer_calculate_result_id asc) as row_num,
	t1.transfer_calculate_result_id as calculate_result_id,
	t1.organization_id,
	csi.serve_item_code,
	t1.number,
	csi.price,
	csi.cost_item_code,
	csi.indirect_cost_bk as indirect_cost_self_organization,
	csi.indirect_cost_gl as indirect_cost_manage_share,
	csi.indirect_cost_yf as indirect_cost_medical_assistance_share,
	csi.medical_cost_type,
	csi.medical_signage,
	t1.ratio,
	t1.num
from
	(
	select
		cr.id as calculate_result_id,
		tcr.id as transfer_calculate_result_id,
		tcr.organization_id,
		csv.operate_serve_number as number,
		csv.ratio,
		csv.num
	from
		cost.pro_cooperation_serve_volume csv
	join cost.pro_cost_cal_res cr on
		cr.check_scheme_id = csv.check_scheme_id
		and cr.organization_id = csv.operate_organization_id
		and cr.serve_item_code = csv.serve_item_code
		and cr.organization_cal_method in (1802)
		and cr.transfer_sign = 0
		and cr.delete_flag = 0
	join cost.pro_cost_cal_res tcr on
		tcr.check_scheme_id = csv.check_scheme_id
		and tcr.organization_id = csv.order_organization_id
		and tcr.serve_item_code = csv.serve_item_code
		and tcr.transfer_organization_id = csv.operate_organization_id
		and tcr.medical_cost_type = cr.medical_cost_type
		and tcr.organization_cal_method in (1802)
		and tcr.transfer_sign = 1
		and tcr.delete_flag = 0
	where
		csv.check_scheme_id = 77
		and csv.organization_calculate_method in (1802) ) t1
join cost.pro_cost_cal_serve_indirect partition(p_csi_77) csi on
	csi.check_scheme_id = 77
	and csi.cal_res_id = t1.calculate_result_id
	and csi.delete_flag = 0;
	
-- 13.关联删除(指定分区)
delete
from cost.pro_cost_cal_serve_indirect partition(p_csi_77) csi
where csi.check_scheme_id = 77
	and exists(
		select 1
		from cost.t_res_all_1802_77 t1
		where t1.id = csi.cal_res_id
		group by 1 
	);

-- 14.关联修改(指定分区)
update
	cost.pro_cost_cal_serve_indirect partition(p_csi_77) 
	set cost.pro_cost_cal_serve_indirect.number = t1.number
from
	cost.pro_cost_cal_serve_indirect partition(p_csi_77)
join cost.t_res_some_1802_77 t1 on
	t1.id = cost.pro_cost_cal_serve_indirect.cal_res_id
where
	cost.pro_cost_cal_serve_indirect.check_scheme_id = 77;